import pymysql
from DBUtils.PooledDB import PooledDB
from..config.database_config import config
from..models.course_model import Course


pool = PooledDB(
    creator=pymysql,
    **config,
    maxconnections=10,
    mincached=2,
    blocking=True
)


# 根据课程编号查询课程信息
def query_course_by_cno(cno):
    try:
        connection = pool.connection()
        cursor = connection.cursor()
        sql = "SELECT * FROM course WHERE Cno = %s"
        cursor.execute(sql, (cno,))
        result = cursor.fetchone()
        if result:
            course = Course(*result)
            cursor.close()
            return course
        connection.close()
    except pymysql.Error as e:
        print(f"查询课程信息时出错: {e}")
    return None


# 插入课程信息
def insert_course(course):
    try:
        connection = pool.connection()
        cursor = connection.cursor()
        sql = "INSERT INTO course (Cno, Cname, Chours, Credit, Tno, StudentCount) VALUES (%s, %s, %s, %s, %s, %s)"
        cursor.execute(sql, (course.cno, course.cname, course.chours, course.credit, course.tno, course.student_count))
        connection.commit()
        cursor.close()
        connection.close()
        print("课程信息插入成功")
    except pymysql.Error as e:
        print(f"插入课程信息时出错: {e}")


# 根据课程编号删除课程信息
def delete_course_by_cno(cno):
    try:
        connection = pool.connection()
        cursor = connection.cursor()
        sql = "DELETE FROM course WHERE Cno = %s"
        cursor.execute(sql, (cno,))
        connection.commit()
        cursor.close()
        connection.close()
        print("课程信息删除成功")
    except pymysql.Error as e:
        print(f"删除课程信息时出错: {e}")


# 根据课程名称查询课程信息（模糊查询）
def query_courses_by_name(cname):
    try:
        connection = pool.connection()
        cursor = connection.cursor()
        sql = "SELECT * FROM course WHERE Cname LIKE %s"
        cursor.execute(sql, (f"%{cname}%",))
        results = cursor.fetchall()
        courses = []
        for result in results:
            course = Course(*result)
            courses.append(course)
        cursor.close()
        connection.close()
        return courses
    except pymysql.Error as e:
        print(f"查询课程信息时出错: {e}")
    return []